//-----------------------------------------------------------------------
// <copyright file="USERDAO.cs" company="Pyramid Consulting">
//     Copyright (c) Pyramid Consulting. All rights reserved.
// </copyright>
//-----------------------------------------------------------------------
	
using System;
using System.Data;
using System.Data.SqlClient;
using MIMS.WRS.Entities;
using MyFramework;
using MyFramework.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace MIMS.WRS.Data
{	
	public class USERDAO : USERDAOBase
	{
		public USERDAO()
		{
		}

//		public static void Test() 
//		{
//			SqlConnection conn = new SqlConnection("server=DI-LAPTOP;uid=sa;pwd=password;database=WRS");
//			conn.Open();
//			conn.Close();
//			conn.Dispose();
//			Database database = DatabaseFactory.CreateDatabase();
//			DBCommandWrapper dbCommandWrapper = database.GetSqlStringCommandWrapper("SELECT * FROM [USER]");			
//
//			DataSet ds = DataAccessHelper.ExecuteDataSet(database, dbCommandWrapper);
//		}

		public virtual USERCollection GetCustomerList()
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERGetCustomerList");
                
			
				USERCollection userCollection = new USERCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						USER user = CreateUSERFromReader(reader);
						userCollection.Add(user);
					}
					reader.Close();
				}
				
				return userCollection;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetUSERsException, ex);
			}
		}

		public virtual USERCollection GetUnreservedCustomerList()
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERGetUnReservedCustomerList");
                
			
				USERCollection userCollection = new USERCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						USER user = CreateUSERFromReader(reader);
						userCollection.Add(user);
					}
					reader.Close();
				}
				
				return userCollection;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetUSERsException, ex);
			}
		}

		public virtual USERCollection GetNonAdminUSERs(USERColumns orderBy, OrderDirection orderDirection, int page, int pageSize, out int totalRecords)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERGetNonAdminList");
                
				dbCommandWrapper.AddInParameter("@OrderBy", DbType.AnsiString, orderBy.ToString());
				dbCommandWrapper.AddInParameter("@OrderDirection", DbType.AnsiString, orderDirection.ToString());
				dbCommandWrapper.AddInParameter("@Page", DbType.Int32, page);
				dbCommandWrapper.AddInParameter("@PageSize", DbType.Int32, pageSize);
				dbCommandWrapper.AddOutParameter("@TotalRecords", DbType.Int32, 4);
                
				USERCollection userCollection = new USERCollection();
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					while (reader.Read())
					{
						USER user = CreateUSERFromReader(reader);
						userCollection.Add(user);
					}
					reader.Close();
				}
				totalRecords = (int)dbCommandWrapper.GetParameterValue("@TotalRecords");
				return userCollection;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetUSERsException, ex);
			}
		}

		public virtual USER GetUSER(string loginName)
		{            
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERGetByLoginName");
                
				dbCommandWrapper.AddInParameter("@LOGIN_NAME", DbType.AnsiString, loginName);
                
				USER user =  null;
				using (IDataReader reader = DataAccessHelper.ExecuteReader(database, dbCommandWrapper))
				{
					if (reader.Read())
					{
						user = CreateUSERFromReader(reader);
						reader.Close();
					}
				}
				return user;
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessGetUSERException, ex);
			}
		}

		#region override methods
		
            
		public override void CreateUSER(USER user)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERInsert");
                
				dbCommandWrapper.AddInParameter("@NAME", DbType.AnsiString, user.NAME);
				dbCommandWrapper.AddInParameter("@LOGIN_NAME", DbType.AnsiString, user.LOGIN_NAME);
				dbCommandWrapper.AddInParameter("@PASSWORD", DbType.AnsiString, user.PASSWORD);
				dbCommandWrapper.AddInParameter("@FullPassword", DbType.AnsiString, user.FullPassword);
				dbCommandWrapper.AddInParameter("@EMAIL", DbType.AnsiString, user.EMAIL);
				dbCommandWrapper.AddInParameter("@USER_TYPE", DbType.Byte, user.USER_TYPE);
				dbCommandWrapper.AddInParameter("@CUSTOMER_CODE", DbType.AnsiString, user.CUSTOMER_CODE);
				dbCommandWrapper.AddInParameter("@BAR_CODE", DbType.AnsiString, user.BAR_CODE);
				dbCommandWrapper.AddInParameter("@IS_ADMIN", DbType.Boolean, user.IS_ADMIN);
				dbCommandWrapper.AddInParameter("@IS_ENABLED", DbType.Boolean, user.IS_ENABLED);
				dbCommandWrapper.AddInParameter("@CREATED_DATE", DbType.DateTime, user.CREATED_DATE);
				dbCommandWrapper.AddInParameter("@CREATED_BY", DbType.Int32, user.CREATED_BY);
				dbCommandWrapper.AddInParameter("@UPDATED_DATE", DbType.DateTime, user.UPDATED_DATE);
				dbCommandWrapper.AddInParameter("@UPDATED_BY", DbType.Int32, user.UPDATED_BY);
				dbCommandWrapper.AddInParameter("@CHANGE_PWD_GUID", DbType.Guid, user.CHANGE_PWD_GUID);
				dbCommandWrapper.AddInParameter("@SORT_REFERENCE1", DbType.AnsiString, user.SORT_REFERENCE1);
				dbCommandWrapper.AddInParameter("@SORT_REFERENCE2", DbType.AnsiString, user.SORT_REFERENCE2);
				dbCommandWrapper.AddInParameter("@DOWNLOADABLE_CSV", DbType.Boolean, user.DOWNLOADABLE_CSV);
				dbCommandWrapper.AddInParameter("@ActiveOfficeID", DbType.Int32, user.ActiveOfficeID);
				dbCommandWrapper.AddInParameter("@CountryID", DbType.Int32, user.CountryID==Nulls.NullInteger?DBNull.Value:(object)user.CountryID);
				dbCommandWrapper.AddInParameter("@ViewSalesOrders", DbType.Boolean, user.ViewSalesOrders);
				dbCommandWrapper.AddOutParameter("@US_ID_PK", DbType.Int32, 0);
                
				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
				user.US_ID_PK = (int)dbCommandWrapper.GetParameterValue("@US_ID_PK");
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessCreateUSERException, ex);
			}
		}

		
        
		public override void UpdateUSER(USER user)
		{
			try
			{
				Database database = DatabaseFactory.CreateDatabase();
				DBCommandWrapper dbCommandWrapper = database.GetStoredProcCommandWrapper("spUSERUpdate");            
                
				dbCommandWrapper.AddInParameter("@US_ID_PK", DbType.Int32, user.US_ID_PK);
				dbCommandWrapper.AddInParameter("@NAME", DbType.AnsiString, user.NAME);
				dbCommandWrapper.AddInParameter("@LOGIN_NAME", DbType.AnsiString, user.LOGIN_NAME);
				dbCommandWrapper.AddInParameter("@PASSWORD", DbType.AnsiString, user.PASSWORD);
				dbCommandWrapper.AddInParameter("@FullPassword", DbType.AnsiString, user.FullPassword);
				dbCommandWrapper.AddInParameter("@EMAIL", DbType.AnsiString, user.EMAIL);
				dbCommandWrapper.AddInParameter("@USER_TYPE", DbType.Byte, user.USER_TYPE);
				dbCommandWrapper.AddInParameter("@CUSTOMER_CODE", DbType.AnsiString, user.CUSTOMER_CODE);
				dbCommandWrapper.AddInParameter("@BAR_CODE", DbType.AnsiString, user.BAR_CODE);
				dbCommandWrapper.AddInParameter("@IS_ADMIN", DbType.Boolean, user.IS_ADMIN);
				dbCommandWrapper.AddInParameter("@IS_ENABLED", DbType.Boolean, user.IS_ENABLED);
				dbCommandWrapper.AddInParameter("@CREATED_DATE", DbType.DateTime, user.CREATED_DATE);
				dbCommandWrapper.AddInParameter("@CREATED_BY", DbType.Int32, user.CREATED_BY);
				dbCommandWrapper.AddInParameter("@UPDATED_DATE", DbType.DateTime, user.UPDATED_DATE);
				dbCommandWrapper.AddInParameter("@UPDATED_BY", DbType.Int32, user.UPDATED_BY);
				dbCommandWrapper.AddInParameter("@CHANGE_PWD_GUID", DbType.Guid, user.CHANGE_PWD_GUID);
				dbCommandWrapper.AddInParameter("@SORT_REFERENCE1", DbType.AnsiString, user.SORT_REFERENCE1);
				dbCommandWrapper.AddInParameter("@SORT_REFERENCE2", DbType.AnsiString, user.SORT_REFERENCE2);
				dbCommandWrapper.AddInParameter("@DOWNLOADABLE_CSV", DbType.Boolean, user.DOWNLOADABLE_CSV);
				dbCommandWrapper.AddInParameter("@ActiveOfficeID", DbType.Int32, user.ActiveOfficeID);
				dbCommandWrapper.AddInParameter("@CountryID", DbType.Int32, user.CountryID==Nulls.NullInteger?DBNull.Value:(object)user.CountryID);
                dbCommandWrapper.AddInParameter("@ViewSalesOrders", DbType.Boolean, user.ViewSalesOrders);

				DataAccessHelper.ExecuteNonQuery(database, dbCommandWrapper);
			}
			catch (Exception ex)
			{
				// log this exception
				Logger.Error(ex);
				// wrap it and rethrow
				throw new DataAccessException(SR.DataAccessUpdateUSERException, ex);
			}
		}
        

		#endregion
	}
}
